"""
基于MSSQL类的存储层实现
"""
from typing import List, Optional, Dict, Any
from datetime import datetime
import logging
from .mssql import MSSQL
from .schemas import SignalCollectionIn, SignalCollectionOut, DeviceInfoIn, DeviceInfoOut, SignalStatisticsOut

logger = logging.getLogger(__name__)

class MSSQLStorage:
    """基于MSSQL类的存储层"""
    
    def __init__(self):
        self.mssql = MSSQL()
    
    def create_signal_collection(self, signal_data: SignalCollectionIn) -> SignalCollectionOut:
        """创建信号采集记录"""
        try:
            # 确保设备存在
            self._ensure_device_exists(signal_data.device_id) 
            
            # 插入信号采集记录
            sql = """
                INSERT INTO signal_collections (
                    device_id, signal_strength, latitude, longitude, altitude, accuracy,
                    network_type, operator_name, cell_id, lac, mcc, mnc,
                    rsrp, rsrq, rssi, snr, collected_at,created_at,uploaded_at, updated_at
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                )
            """
            
            now = datetime.utcnow()
            params = (
                signal_data.device_id,
                signal_data.signal_strength,
                signal_data.latitude,
                signal_data.longitude,
                signal_data.altitude,
                signal_data.accuracy,
                signal_data.network_type,
                signal_data.operator_name,
                signal_data.cell_id,
                signal_data.lac,
                signal_data.mcc,
                signal_data.mnc,
                signal_data.rsrp,
                signal_data.rsrq,
                signal_data.rssi,
                signal_data.snr,
                signal_data.collected_at,
                now,
                now,
                now  # updated_at 字段
            )
            
            # 使用参数化查询防止SQL注入
            self.mssql.ExecNonQuery(sql, params)
            
            # 获取刚插入的记录ID
            get_id_sql = """
                SELECT TOP 1 id FROM signal_collections 
                WHERE device_id = %s AND uploaded_at = %s 
                ORDER BY id DESC
            """
            result = self.mssql.ExecQuery(get_id_sql, (signal_data.device_id, now))
            
            if result:
                record_id = result[0][0]
                
                # 更新设备最后在线时间
                self._update_device_last_seen(signal_data.device_id)
                
                logger.info(f"信号采集记录创建成功: {record_id}")
                
                # 返回创建的记录
                return self._get_signal_collection_by_id(record_id)
            else:
                raise Exception("无法获取新创建的记录ID")
                
        except Exception as e:
            logger.error(f"创建信号采集记录失败: {e}")
            raise
    
    def get_signal_collections(
        self, 
        device_id: Optional[str] = None,
        start_time: Optional[datetime] = None,
        end_time: Optional[datetime] = None,
        limit: int = 100,
        offset: int = 0
    ) -> List[SignalCollectionOut]:
        """获取信号采集记录列表"""
        try:
            sql = """SELECT id,device_id, signal_strength, latitude, longitude, altitude, accuracy,network_type, operator_name, cell_id, 
          lac, mcc, mnc,rsrp, rsrq, rssi, snr, collected_at,created_at,uploaded_at, updated_at FROM signal_collections WHERE 1=1"""
            params = []
            
            if device_id:
                sql += " AND device_id = %s"
                params.append(device_id)
            
            if start_time:
                sql += " AND collected_at >= %s"
                params.append(start_time)
            
            if end_time:
                sql += " AND collected_at <= %s"
                params.append(end_time)
            
            sql += " ORDER BY collected_at DESC"
            
            # 使用兼容性更好的分页语法
            if offset > 0 or limit < 1000:  # 如果设置了分页参数
                # 使用子查询实现分页，兼容更多SQL Server版本
                sql = f"""
                    SELECT id,device_id, signal_strength, latitude, longitude, altitude, accuracy,network_type, operator_name, cell_id,
                        lac, mcc, mnc,rsrp, rsrq, rssi, snr, collected_at,created_at,uploaded_at, updated_at FROM (
                        SELECT ROW_NUMBER() OVER (ORDER BY collected_at DESC) AS RowNum, *
                        FROM signal_collections
                        WHERE 1=1 {self._build_where_clause(device_id, start_time, end_time)}
                    ) AS T
                    WHERE RowNum > {offset} AND RowNum <= {offset + limit}
                """
            else:
                # 如果没有分页参数，添加TOP限制
                sql = f"""SELECT TOP {limit} id,device_id, signal_strength, latitude, longitude, altitude, accuracy,network_type, operator_name, cell_id,         lac, mcc, mnc,rsrp, rsrq, rssi, snr, collected_at,created_at,uploaded_at, updated_at FROM signal_collections WHERE 1=1"""
                if device_id:
                    sql += " AND device_id = %s"
                if start_time:
                    sql += " AND collected_at >= %s"
                if end_time:
                    sql += " AND collected_at <= %s"
                sql += " ORDER BY collected_at DESC"
            
            results = self.mssql.ExecQuery(sql, tuple(params) if params else None)
            
            collections = []
            for row in results:
                collection = self._row_to_signal_collection(row)
                collections.append(collection)
            
            return collections
            
        except Exception as e:
            logger.error(f"获取信号采集记录失败: {e}")
            raise
    
    def get_signal_statistics(self, device_id: str) -> Optional[SignalStatisticsOut]:
        """获取设备信号统计信息"""
        try:
            sql = """
                SELECT 
                    device_id,
                    COUNT(*) as total_collections,
                    AVG(CAST(signal_strength AS FLOAT)) as avg_signal_strength,
                    MIN(signal_strength) as min_signal_strength,
                    MAX(signal_strength) as max_signal_strength,
                    MIN(collected_at) as first_collection,
                    MAX(collected_at) as last_collection,
                    COUNT(DISTINCT CAST(collected_at AS DATE)) as collection_days
                FROM signal_collections 
                WHERE device_id = %s
                GROUP BY device_id
            """
            
            result = self.mssql.ExecQuery(sql, (device_id,))
            
            if result:
                row = result[0]
                return SignalStatisticsOut(
                    device_id=row[0],
                    total_collections=row[1],
                    avg_signal_strength=float(row[2]) if row[2] else 0.0,
                    min_signal_strength=row[3],
                    max_signal_strength=row[4],
                    first_collection=row[5],
                    last_collection=row[6],
                    collection_days=row[7]
                )
            
            return None
            
        except Exception as e:
            logger.error(f"获取信号统计信息失败: {e}")
            raise
    
    def register_device(self, device_info: DeviceInfoIn) -> DeviceInfoOut:
        """注册设备信息"""
        try:
            # 检查设备是否已存在
            check_sql = "SELECT * FROM devices WHERE device_id = %s"
            existing = self.mssql.ExecQuery(check_sql, (device_info.device_id,))
            
            if existing:
                # 更新现有设备信息
                update_sql = """
                    UPDATE devices SET 
                        device_name = %s,
                        device_model = %s,
                        os_version = %s,
                        app_version = %s,
                        last_seen = %s,
                        is_active = 1,
                        updated_at = %s
                    WHERE device_id = %s
                """
                now = datetime.utcnow()
                params = (
                    device_info.device_name,
                    device_info.device_model,
                    device_info.os_version,
                    device_info.app_version,
                    now,
                    now,
                    device_info.device_id
                )
                
                self.mssql.ExecNonQuery(update_sql, params)
                
                logger.info(f"设备信息更新成功: {device_info.device_id}")
                return self._get_device_by_id(device_info.device_id)
            else:
                # 创建新设备
                insert_sql = """
                    INSERT INTO devices (
                        device_id, device_name, device_model, os_version, 
                        app_version, last_seen, is_active, created_at, updated_at
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, 1, %s, %s
                    )
                """
                now = datetime.utcnow()
                params = (
                    device_info.device_id,
                    device_info.device_name,
                    device_info.device_model,
                    device_info.os_version,
                    device_info.app_version,
                    now,
                    now,
                    now
                )
                
                self.mssql.ExecNonQuery(insert_sql, params)
                
                logger.info(f"设备注册成功: {device_info.device_id}")
                return self._get_device_by_id(device_info.device_id)
                
        except Exception as e:
            logger.error(f"设备注册失败: {e}")
            raise
    
    def _ensure_device_exists(self, device_id: str):
        """确保设备存在"""
        check_sql = "SELECT COUNT(*) FROM devices WHERE device_id = %s"
        result = self.mssql.ExecQuery(check_sql, (device_id,))
        
        if result and result[0][0] == 0:
            # 创建设备记录
            insert_sql = """
                INSERT INTO devices (
                    device_id, device_name, is_active, last_seen, created_at, updated_at
                ) VALUES (
                    %s, %s, 1, %s, %s, %s
                )
            """
            now = datetime.utcnow()
            params = (
                device_id,
                f"设备_{device_id}",
                now,
                now,
                now
            )
            
            self.mssql.ExecNonQuery(insert_sql, params)
            logger.info(f"自动创建设备记录: {device_id}")
    
    def _update_device_last_seen(self, device_id: str):
        """更新设备最后在线时间"""
        update_sql = "UPDATE devices SET last_seen = %s WHERE device_id = %s"
        now = datetime.utcnow()
        self.mssql.ExecNonQuery(update_sql, (now, device_id))
    
    def _get_signal_collection_by_id(self, record_id: int) -> SignalCollectionOut:
        """根据ID获取信号采集记录"""
        sql = """SELECT id,device_id, signal_strength, latitude, longitude, altitude, accuracy,network_type, operator_name, cell_id, 
          lac, mcc, mnc,rsrp, rsrq, rssi, snr, collected_at,created_at,uploaded_at, updated_at FROM signal_collections WHERE id = %s"""
        result = self.mssql.ExecQuery(sql, (record_id,))
        
        if result:
            return self._row_to_signal_collection(result[0])
        else:
            raise Exception(f"未找到ID为{record_id}的记录")
    
    def _get_device_by_id(self, device_id: str) -> DeviceInfoOut:
        """根据设备ID获取设备信息"""
        sql = """SELECT id,device_id, device_name, device_model, os_version, 
                        app_version, last_seen, is_active, created_at, updated_at FROM devices WHERE device_id = %s"""
        result = self.mssql.ExecQuery(sql, (device_id,))
        
        if result:
            return self._row_to_device_info(result[0])
        else:
            raise Exception(f"未找到设备ID为{device_id}的设备")
    
    def _row_to_signal_collection(self, row) -> SignalCollectionOut:
        """将数据库行转换为SignalCollectionOut对象"""
        return SignalCollectionOut(
            id=row[0],
            device_id=row[1],
            signal_strength=row[2],
            latitude=row[3],
            longitude=row[4],
            altitude=row[5],
            accuracy=row[6],
            network_type=row[7],
            operator_name=row[8],
            cell_id=row[9],
            lac=row[10],
            mcc=row[11],
            mnc=row[12],
            rsrp=row[13],
            rsrq=row[14],
            rssi=row[15],
            snr=row[16],
            collected_at=row[17],
            uploaded_at=row[18],
            created_at=row[19],
            updated_at=row[20] if len(row) > 20 else None
        )
    
    def _row_to_device_info(self, row) -> DeviceInfoOut:
        """将数据库行转换为DeviceInfoOut对象"""
        return DeviceInfoOut(
            id=row[0],
            device_id=row[1],
            device_name=row[2],
            device_model=row[3],
            os_version=row[4],
            app_version=row[5],
            last_seen=row[6],
            is_active=row[7],
            created_at=row[8],
            updated_at=row[9]
        )
    
    def _build_where_clause(self, device_id: Optional[str], start_time: Optional[datetime], end_time: Optional[datetime]) -> str:
        """构建WHERE子句"""
        where_clause = ""
        if device_id:
            where_clause += f" AND device_id = '{device_id}'"
        if start_time:
            where_clause += f" AND collected_at >= '{start_time}'"
        if end_time:
            where_clause += f" AND collected_at <= '{end_time}'"
        return where_clause

# 全局存储实例
mssql_storage = MSSQLStorage()